﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using FTchina;
using System.Collections;
using System.Data.OleDb;
using System.Data;

public partial class UI_import : System.Web.UI.Page
{
    string GUID = new Guid().ToString();//流程的GUID编号
    string PATH = string.Empty;//上传文件的保持文件名，绝对路径。如D:\BPM\SureBpm-Ex\SureBpm.Web\flow\dy\zhgl\tzgl\temp\23236.xls
    protected void Page_Load(object sender, EventArgs e)
    {



        PATH = Server.MapPath("~/uploads/");



    }




    protected void Excel2Sqls(string filepath)
    {
        bool flag = true;//查看是否有问题
        string FalseCode = string.Empty;//记录有错误的号码

        #region 插入的sql语句

        string insertsql = @"INSERT INTO [CustomerList]
           ([CustomerName]
           ,[CustormeSex]
           ,[CustomerTel0]
           ,[Customerlandline0]         
           ,[CustomerAddress0]
           ,[CustomerWork]
           ,[Memo]
,[CustomerGroupID]
           ,[Callcount]
           ,[MeetCount]
           ,[LastCallDate]
           ,[LastMeetDate])
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'         
           ,'{3}'         
           ,'{4}'
           ,'{5}'
           ,'{6}','0','0','0','" + basic.GetSqliteDate(new DateTime(1910, 1, 1)) + "','" + basic.GetSqliteDate(new DateTime(1910, 1, 1)) + "')";
        #endregion
        //将上传的文件当做数据库处理
        string connectionStringFormat = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties='Excel 8.0;HDR=Yes;imex=1'";
        string connectString = string.Format(connectionStringFormat, filepath);

        DataSet myDs = new DataSet();

        try
        {
            myDs.Tables.Clear();
            myDs.Clear();
            OleDbConnection cnnxls = new OleDbConnection(connectString);
            OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
            myDa.Fill(myDs, "c");


            // Response.Write( myDs.Tables[0].Rows.Count.ToString());
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

        //姓名	性别	手机	固定电话	住址	单位	说明

        ArrayList al = new ArrayList();

        //获取每一行数据，并进行格式校验
        string CustomerName = string.Empty;
        string CustormeSex = string.Empty;
        string CustomerTel0 = string.Empty;
        string Customerlandline0 = string.Empty;
        string CustomerAddress0 = string.Empty;
        string CustomerWork = string.Empty;
        string Memo = string.Empty;


        try
        {
            foreach (DataRow dr in myDs.Tables[0].Rows)
            {
                CustomerName = dr["姓名"].ToString().Trim();

                if (CustomerName.Length == 0)
                {
                    flag = false;
                    FalseCode = " 姓名 必填 ";
                    break;
                }

                CustormeSex = dr["性别"].ToString().Trim();
                CustomerTel0 = dr["手机"].ToString().Trim();
                Customerlandline0 = dr["固定电话"].ToString().Trim();
                CustomerAddress0 = dr["住址"].ToString().Trim();
                CustomerWork = dr["单位"].ToString().Trim();
                Memo = dr["说明"].ToString().Trim();
                string checkstring = CustomerName + CustormeSex + CustomerTel0 + Customerlandline0 + CustomerAddress0 + CustomerWork + Memo;
                if (checkstring.Length == 0) continue;//如果是空行则忽略



                string execsql = string.Format(insertsql, CustomerName, CustormeSex, CustomerTel0, Customerlandline0, CustomerAddress0, CustomerWork, Memo);
                al.Add(execsql);
            }

        }
        catch (System.Exception er)
        {
            JS.Alert("请下载模板文件进行更新，否则无法正常导入" + er.Message);
        }
        //Response.Write(flag);


        //如果都没有错误就执行批量导入，数据库事务执行
        if (!flag)
        {
            JS.Alert("姓名为必填，请重新检查，或保持格式正确");
        }
        else
        {
            JS.Alert(shujuku.ExecuteSqlTransaction(al) ? "处理成功！" : "处理失败！");
            JS.CloseOpenerWindow();
            JS.CloseWindow();

        }
    }






    protected void Button1_Click(object sender, EventArgs e)
    {
        if (!Directory.Exists(PATH)) Directory.CreateDirectory(PATH);//创建目录

        FileUpload1.SaveAs(PATH + GUID + ".xls");

        Excel2Sqls(PATH + GUID + ".xls");
    }

}